import { Inject, Injectable } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource, EntityTarget, ObjectLiteral } from 'typeorm';
import * as XLSX from 'xlsx';
import { v4 as uuidv4 } from 'uuid';
import { createTransport } from 'nodemailer';
import type {
    DeleteDto,
    FormFieldSelectOption,
    ResponseJson,
    SqlQueryDto,
} from '@/common/type/app.dto';
import {
    Client,
    CntrSize,
    CntrStat,
    CntrType,
    CyCfs, Scode,
    UserQueryCfg
} from '@/basedata/entities/basedatum.entity';
import { ResponseCode } from '@/common/type/enum';
import { EmailType } from '@/common/type/app.d';
import {
    Category,
    Currency,
    FeeCod, FeeTyp,
    FeeTypCod,
    Invoice
} from '@/business/entities/business.entity';
import { CrudService } from './crud.service';
import { getExcelColumnName } from '@/common/tool/dbhelper';
import { join } from 'node:path';
import { transFormatter } from '@/common/tool/trans';
import { UserQueryCfgDto } from '@/basedata/dto/create-basedatum.dto';
@Injectable()
export class SharedService {
    constructor(@InjectDataSource() private datasource: DataSource,
        private crud: CrudService,
        @Inject('downloadPath') private readonly downloadpath: string) { }
    // 邮件对象
    transporter = createTransport({
        host: "smtp.163.com",
        port: 465,
        secure: true, // true for 465, false for other ports
        auth: {
            user: 'ediupload@163.com', // generated ethereal user
            pass: 'HSFDSZIKZYVYQPTW', // generated ethereal password
        },
    });

    async sendMail(opt: EmailType): Promise<string> {
        try {
            if (opt.to) {
                opt.to.replaceAll('；', ';');
            } else {
                return 'email接收地址为空';
            }
            opt.from = '<ediupload@163.com>';
            opt.cc = 'yizhou.public@yizhoucontainer.com';
            // opt.replyTo = 'yizhou.public@yizhoucontainer.com'; // 设置回复地址
            const info = await this.transporter.sendMail(opt);
            // console.log('邮件发送结果:', info);
            return 'SUCCESS';
        } catch (error) {
            console.log('邮件发送错误', error);
        }
        return '邮件发送错误';
    }

    /**
     * 不要改！！！！！！！！
     * 使用点： 前端刷新基础数据、 后端导出excel文件  
     * @returns 
     */
    async getFormFieldSelectOption(): Promise<
        ResponseJson<
            {
                [key: string]: Array<FormFieldSelectOption>;
            },
            null
        >
    > {
        const result: {
            [key: string]: Array<FormFieldSelectOption>;
        } = {};

        result['CntrSiz'] = [];
        const rCntrSiz = await this.datasource
            .createQueryBuilder()
            .select(['cntr_siz_cod'])
            .from<CntrSize>(CntrSize, 'c_cntr_siz')
            .execute();
        rCntrSiz.forEach((e) => {
            result['CntrSiz'].push({
                value: e.cntr_siz_cod,
                label: e.cntr_siz_cod,
            });
        });
        result['CntrTyp'] = [];
        const rCntrTyp = await this.datasource
            .createQueryBuilder()
            .select(['cntr_typ_cod', 'cntr_typ_nam_c'])
            .from<CntrType>(CntrType, 'c_cntr_typ')
            .execute();
        rCntrTyp.forEach((e) => {
            result['CntrTyp'].push({
                value: e.cntr_typ_cod,
                label: e.cntr_typ_cod,
            });
        });
        result['Client'] = [];
        result['ClientKeyValue'] = [];   // 导出excel文件 label-value  转换使用
        const rClient = await this.datasource
            .createQueryBuilder()
            .select([
                'id',
                'client_nam_c',
                'trans_corp_id',
                'provid_id',
                'cntr_owner_id',
                'repair_id',
                'custom_id',
            ])
            .from<Client>(Client, 'c_client_cod')
            // .orderBy('client_nam_index')
            .execute();
        result['Client'] = rClient;
        rClient.forEach((element) => {
            result['ClientKeyValue'].push({
                value: element.id,
                label: element.client_nam_c,
            });
        });

        result['CntrDam'] = [];
        const rCntrDam = await this.datasource
            .createQueryBuilder()
            .select(['code', 'name'])
            .from<Scode>(Scode, 's_code')
            .where('fld_eng = :fld_eng', { fld_eng: 'DAM_ID' })
            .execute();
        rCntrDam.forEach((element) => {
            result['CntrDam'].push({
                value: element.code,
                label: element.name,
            });
        });

        
        
        result['Cfs'] = [];
        const rCfs = await this.datasource
            .createQueryBuilder()
            .select(['id', 'cfs_nam_c'])
            .from<CyCfs>(CyCfs, 'c_cy_cfs')
            .execute();
        rCfs.forEach((element) => {
            result['Cfs'].push({
                value: element.id,
                label: element.cfs_nam_c,
            });
        });

        result['CntrStat'] = [];
        const rCntrStat = await this.datasource
            .createQueryBuilder()
            .select(['stat_cod', 'stat_nam', 'carry_id'])
            .from<CntrStat>(CntrStat, 'c_cntr_stat')
            .where('carry_id is not null')
            .execute();
        result['CntrStat'] = rCntrStat;


        /* rCntrStat.forEach((element) => {
            result['CntrStat'].push({
                value: element.stat_cod,
                label: element.stat_nam,
            });
        }); */

        /* result['Place'] = [];
        const rPlace = await this.datasource
            .createQueryBuilder()
            .select(['id', 'plac_nam'])
            .from<Place>(Place, 'c_plac_cod')
            .orderBy('plac_short')
            .execute();
        rPlace.forEach((element) => {
            result['Place'].push({
                value: element.id,
                label: element.plac_nam,
            });
        }); */

        result['CntrStatus'] = [];
        const rCntrStatus = await this.datasource
            .createQueryBuilder()
            .select(['code', 'name'])
            .from<Scode>(Scode, 's_code')
            .where('fld_eng = :fld_eng', { fld_eng: 'CURRENT_STAT' })
            .execute();
        rCntrStatus.forEach((element) => {
            result['CntrStatus'].push({
                value: element.code,
                label: element.name,
            });
        });


        /* result['RefMachine'] = [];
        const rRefMachine = await this.datasource
            .createQueryBuilder()
            .select(['code', 'name'])
            .from<Scode>(Scode, 's_code')
            .where('fld_eng = :fld_eng', { fld_eng: 'REF_MACHINE' })
            .execute();
        rRefMachine.forEach((element) => {
            result['RefMachine'].push({
                value: element.code,
                label: element.name,
            });
        }); */
        result['Currency'] = [];
        const rCurrency = await this.datasource
            .createQueryBuilder()
            .select(['currency_cod', 'currency_nam'])
            .from<Currency>(Currency, 'c_rmb_rate')
            .execute();
        rCurrency.forEach((element) => {
            result['Currency'].push({
                value: element.currency_cod,
                label: element.currency_nam,
            });
        });
        // 资金账号
        result['Category'] = [];
        const rCategory = await this.datasource
            .createQueryBuilder()
            .select(['id', 'cate_item_nam', 'currency_cod'])
            .from<Category>(Category, 'c_category')
            .execute();
        rCategory.forEach((element) => {
            result['Category'].push({
                value: element.id,
                label: element.cate_item_nam,
                currency_cod: element.currency_cod,
            });
        });
        // 费用类型
        result['FeeTyp'] = [];
        const rFeeTyp = await this.datasource
            .createQueryBuilder()
            .select(['fee_typ_cod', 'fee_typ_nam'])
            .from<FeeTyp>(FeeTyp, 'c_fee_typ')
            .execute();
        rFeeTyp.forEach((element) => {
            result['FeeTyp'].push({
                value: element.fee_typ_cod,
                label: element.fee_typ_nam,
            });
        });
        // 费目
        result['FeeCod'] = [];
        const rFeeCod = await this.datasource
            .createQueryBuilder()
            .select(['fee_cod', 'fee_nam'])
            .from<FeeCod>(FeeCod, 'c_fee_cod')
            .execute();
        rFeeCod.forEach((element) => {
            result['FeeCod'].push({
                value: element.fee_cod,
                label: element.fee_nam,
            });
        });
        // 关联二级费目
        result['FeeTypCod'] = [];

        for (let index = 0; index < result['FeeTyp'].length; index++) {
            const feetyp = result['FeeTyp'][index];
            const feecods = await this.datasource.createQueryBuilder<FeeTypCod>(FeeTypCod, 'c_fee_typ_cod')
                .select(['c_fee_typ_cod.fee_cod as fee_cod'])
                .where('c_fee_typ_cod.fee_typ_cod = :feetyp', { feetyp: feetyp.value })
                .execute();
            // console.log(feetyp, feecods);

            const children = [];
            for (let i = 0; i < feecods.length; i++) {
                const feecod = feecods[i];
                const findfeecod = result['FeeCod'].find((e) => {
                    return e.value === feecod['fee_cod'];
                });
                if (findfeecod) {
                    children.push(findfeecod);
                }
            }
            feetyp['children'] = children;
            result['FeeTypCod'].push(feetyp);

        }
        // console.log('result[FeeTypCod]', result['FeeTypCod']);

        result['TransKind'] = [];
        const rTransKind = await this.datasource
            .createQueryBuilder()
            .select(['code', 'name'])
            .from<Scode>(Scode, 's_code')
            .where('fld_eng = :fld_eng', { fld_eng: 'TRANS_KIND' })
            .execute();
        rTransKind.forEach((element) => {
            result['TransKind'].push({
                value: element.code,
                label: element.name,
            });
        });

        result['PayKind'] = [];
        const rPayKind = await this.datasource
            .createQueryBuilder()
            .select(['code', 'name'])
            .from<Scode>(Scode, 's_code')
            .where('fld_eng = :fld_eng', { fld_eng: 'PAY_KIND' })
            .execute();
        rPayKind.forEach((element) => {
            result['PayKind'].push({
                value: element.code,
                label: element.name,
            });
        });

        return {
            code: ResponseCode.SuccessNoMsg,
            obj: result,
        };
    }
    /**
     * 过滤发票户头名称  
     * @param opt   type: from/to
     * @returns 
     */
    async filterInvoiceFromCorpNam(opt: { type: string, queryString: string }): Promise<ResponseJson<{ value: string }[]>> {
        let r: any;
        const qstr = `%${opt.queryString}%`;
        if (opt.type === 'from') {
            r = await this.datasource.manager.createQueryBuilder<Invoice>(Invoice, 'invoice')
                .select(['from_corp_nam as value'])
                .where("from_corp_nam like :nam", { nam: qstr })
                .execute();
        } else {
            r = await this.datasource.manager.createQueryBuilder<Invoice>(Invoice, 'invoice')
                .select(['to_corp_nam as value'])
                .where("to_corp_nam like :nam", { nam: qstr })
                .execute();
        }


        return {
            code: ResponseCode.SuccessNoMsg,
            obj: r
        }
    }
    /* async getFeeFieldSelectOption(): Promise<
        ResponseJson<
            {
                [key: string]: Array<FormFieldSelectOption>;
            },
            null
        >
    > {
        const result: {
            [key: string]: Array<FormFieldSelectOption>;
        } = {};

        result['Protocol'] = [];
        const rProtocol = await this.datasource
            .createQueryBuilder()
            .select(['protocol_cod', 'protocol_nam'])
            .from<Protocol>(Protocol, 'c_protocol')
            .execute();
        rProtocol.forEach((element) => {
            result['Protocol'].push({
                value: element.protocol_cod,
                label: element.protocol_nam,
            });
        });

        result['Mod'] = [];
        const rMod = await this.datasource
            .createQueryBuilder()
            .select(['fee_mod_cod', 'fee_mod_nam'])
            .from<FeeMod>(FeeMod, 'c_fee_mod')
            .execute();
        rMod.forEach((element) => {
            result['Mod'].push({
                value: element.fee_mod_cod,
                label: element.fee_mod_nam,
            });
        });

        result['Ele'] = [];
        const rEle = await this.datasource
            .createQueryBuilder()
            .select(['fee_ele', 'c_fld_nam'])
            .from<FeeEle>(FeeEle, 'c_fee_ele')
            .execute();
        rEle.forEach((element) => {
            result['Ele'].push({
                value: element.fee_ele,
                label: element.c_fld_nam,
            });
        });

        return {
            code: ResponseCode.SuccessNoMsg,
            obj: result,
        };
    } */
    /**
     * 
     * @param entity 
     * @param query 
     * @param transKeys 需要转换的字段, key字段名称 value是getFormFieldSelectOption()返回的对象的key
     * @returns 
     */
    async exportQueryToExcel<T extends ObjectLiteral>(
        entity: EntityTarget<T>,
        query: SqlQueryDto,
        transMaps: { [key: string]: string },

    ): Promise<ResponseJson<string, null>> {
        const transKeys = Object.keys(transMaps);
        const pagerow = 200;
        const excelName = uuidv4() + '.xlsx';
        const downloadFileName = '/download/' + excelName;
        query.skip = 0;
        query.take = pagerow;
        let queryResult = await this.crud.query<T>(entity, query);
        if (queryResult.ext > 0) {
            const queryTiems = Math.ceil(queryResult.ext / pagerow);

            if (query.excelColumn?.length > 0) {
                const columnNamesMap = getExcelColumnName(this.datasource.getMetadata(entity), query.excelColumn);
                const transData = await this.getFormFieldSelectOption();
                const wb = XLSX.utils.book_new(); // 创建一个新的空工作簿
                const ws = XLSX.utils.json_to_sheet([]);
                // const ws = XLSX.utils.json_to_sheet([ columnNames ], {header: Object.values(columnNames)}); // 创建一个包含标题的工作表   
                XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
                const excelFilePath = join(this.downloadpath, excelName);
                XLSX.writeFile(wb, excelFilePath);
                // 循环查询
                for (let index = 0; index < queryTiems; index++) {
                    const workbook = XLSX.readFile(excelFilePath, {
                        type: 'binary',
                        cellDates: true,
                    });

                    if (workbook.SheetNames.length === 0) {
                        throw new Error('空文件');
                    }
                    const firstSheetName = workbook.SheetNames[0]; // 读取第一个工作表
                    const sheet = workbook.Sheets[firstSheetName];
                    if (index === 0) {
                        // 写入列头
                        const element = queryResult.obj[0];
                        const headers: any = {};
                        for (const key in element) {
                            if (!query.excelColumn.includes(key)) {
                                Reflect.deleteProperty(element, key);
                            } else {
                                headers[key] = columnNamesMap[key];
                            }
                        }
                        XLSX.utils.sheet_add_json(sheet, [headers], { skipHeader: true, origin: -1 });

                    } else {
                        query.skip = index * pagerow;
                        query.take = pagerow;
                        queryResult = await this.crud.query<T>(entity, query, { sum: false });
                    }

                    // 循环查询返回的数据
                    for (let i = 0; i < queryResult.obj.length; i++) {
                        const element = queryResult.obj[i];
                        // console.log('element', element);

                        // 循环每行数据
                        for (const key in element) {
                            if (!query.excelColumn.includes(key)) {
                                Reflect.deleteProperty(element, key);
                            } else {
                                // 处理转换
                                if (transKeys.includes(key)) {
                                    (element)[key] = transFormatter(element[key], transData.obj[transMaps[key as string]]);
                                }
                                /* if (key === 'current_stat') {
                                    (element as any)['current_stat'] = transFormatter(element['current_stat'], transData.obj['CntrStatus']);
                                } */
                            }
                        }
                        XLSX.utils.sheet_add_json(sheet, [element], { skipHeader: true, origin: -1 });
                    }
                    XLSX.writeFile(workbook, excelFilePath);
                }

            } else {
                return {
                    code: ResponseCode.FailWithMsg,
                    msg: ['未传入导出列名称列表']
                }
            }


        }

        return {
            code: ResponseCode.SuccessNoMsg,
            obj: downloadFileName
        };
    }

    async saveUserQueryCfg(dto: UserQueryCfgDto): Promise<ResponseJson<UserQueryCfg, null>> {
        const queryRunner = this.datasource.createQueryRunner();
        await queryRunner.connect();
        await queryRunner.startTransaction();
        try {
            const r = await queryRunner.manager.createQueryBuilder()
                .insert()
                .into(UserQueryCfg)
                .values(dto)
                 .orUpdate(
                    ['cfg_json'],
                    ['user_name', 'table_id', 'item_name']
                 )
                .execute();
            await queryRunner.commitTransaction();
            return {
                code: ResponseCode.SuccessWithInfo,
                msg: ['保存成功']
            }
        } catch (err) {
            await queryRunner.rollbackTransaction();
            throw err;
        } finally {
            await queryRunner.release();
        }
        
    }

    async deleteUserQueryCfg(dto: DeleteDto): Promise<ResponseJson> {
        return await this.crud.delete<UserQueryCfg>(UserQueryCfg, dto);
    }

    async queryUserQueryCfg(
        query: SqlQueryDto,
        oper_nam: string
    ): Promise<ResponseJson<UserQueryCfg[], number>> {
        query.where['user_name'] = {
            "equals": oper_nam
        }
        return await this.crud.query<UserQueryCfg>(UserQueryCfg, query);
    }
}
